package battleship;

import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.mysql.jdbc.Connection;
import com.mysql.jdbc.PreparedStatement;
import com.mysql.jdbc.Statement;

public class Databasetest {

	/**
	 * @param args
	 */
	public static void main(String[] args) {

		 try {
		 Statement stmt;
		 ResultSet rs;

		 //Register the JDBC driver for MySQL.
		 Class.forName("com.mysql.jdbc.Driver");
		 
//		 String url =
//				 "jdbc:mysql://localhost:3306/JunkDB";
//		 
//		 Connection con =
//				 (Connection) DriverManager.getConnection(
//		 url,"auser", "drowssap");
		 
//		 String url =
//				 "jdbc:mysql://localhost:3306/mysql";
//		 Connection con =
//				 (Connection) DriverManager.getConnection(
//		 url,"root", "");
		 
		 
		 String url =  "jdbc:mysql://mysql-tartubattleship.jelastic.dogado.eu/junkdb";
		 Connection con = (Connection) DriverManager.getConnection(url, "root", "5mfITFh1rb");
		 
		 
		 //Display URL and connection information
		 System.out.println("URL: " + url);
		 System.out.println("Connection: " + con);

		 //Get a Statement object
		 stmt = (Statement) con.createStatement();
		 try {
		 stmt.executeUpdate(
				 "DROP DATABASE JunkDB");
		 }catch (SQLException e) {
				System.out.println("SQLException: " + e.getMessage());
				System.out.println("SQLState: "+ e.getSQLState());
				System.out.println("VendorError: " + e.getErrorCode());
			 
		 }

					//	 con.close();
						 }catch( Exception e ) {
						 e.printStackTrace();
						 }//end catch
						 }//end main
	
	
	
	 public static List<UserWinLoseInformation> getScores(){
	//	 CreateDatabase.create();
		 List<UserWinLoseInformation> usersWinLoseInformation = new ArrayList<UserWinLoseInformation>();
		 try {
			 Statement stmt;
			 ResultSet rs;

			
			 Class.forName("com.mysql.jdbc.Driver");
			 
			 String url = "jdbc:mysql://localhost:3306/JunkDB";
			 
			 Connection con = (Connection) DriverManager.getConnection(url,"auser", "drowssap");
			 
//			String url =  "jdbc:mysql://mysql-tartubattleship.jelastic.dogado.eu/junkdb";
//			 Connection con = (Connection) DriverManager.getConnection(url, "root", "5mfITFh1rb");
			// Connection con = (Connection) DriverManager.getConnection(url, "kasutaja", "dbparool1");
			 
			 
			 //Display URL and connection information
			 System.out.println("URL: " + url);
			 System.out.println("Connection: " + con);

			 //Get a Statement object
			 stmt = (Statement) con.createStatement();
			 rs = stmt.executeQuery("SELECT * FROM testtable ORDER BY winpoints DESC;");
			 
			while (rs.next()) {
					String name = rs.getString("name");
					Integer winPoints = rs.getInt("winpoints");
					Integer losePoints = rs.getInt("losepoints");
					UserWinLoseInformation userWinLoseInstance = new UserWinLoseInformation(name, winPoints, losePoints);
					usersWinLoseInformation.add(userWinLoseInstance);
				}
			
			 ResultSet rs2 = stmt.executeQuery("SELECT * " +
					 "from testtable ORDER BY winpoints");
			 
//			 System.out.println("Display all results:");
//			 while(rs2.next()){
//			 int theInt= rs2.getInt("winpoints");
//			 int theInt2= rs2.getInt("losepoints");
//			 String str = rs2.getString("name");
//			 System.out.println("\twinpoints= " + theInt +"\tlosepoints= " + theInt2 
//			 + "\tname = " + str);
//			 }
			 
			 con.close();
			 return usersWinLoseInformation;
			 
			 }catch( Exception e ) {
			    e.printStackTrace();
			 }
		 return usersWinLoseInformation;
	 }
	 
	 public static void addScores(String user, int winpoints){
	//	 CreateDatabase.create();
		 try {
			 Statement stmt;
			 ResultSet rs;
		//	ResultSet rs2;
		//	Statement stmt2;

			
			 Class.forName("com.mysql.jdbc.Driver");
			 
			 String url =
					 "jdbc:mysql://localhost:3306/JunkDB";
			 
			 Connection con =
					 (Connection) DriverManager.getConnection(
			 url,"auser", "drowssap");
			 
			 
//			 String url =  "jdbc:mysql://mysql-tartubattleship.jelastic.dogado.eu/junkdb";
//			 Connection con = (Connection) DriverManager.getConnection(url, "root", "5mfITFh1rb");
//			 
//			 Connection con = (Connection) DriverManager.getConnection(url, "kasutaja", "dbparool1");
			 
			 
			 //Display URL and connection information
			 System.out.println("URL: " + url);
			 System.out.println("Connection: " + con);

			 //Get a Statement object
			 stmt = (Statement) con.createStatement();
			// stmt2 = (Statement) con.createStatement();
			
			 rs= stmt.executeQuery("SELECT winpoints,losepoints FROM testtable WHERE name=\""+ user + "\";");
			 
			 
				if (rs.next()) {
					int winPoints = rs.getInt("winpoints");
					winPoints += winpoints;
					try {
						String statement = "UPDATE testtable set winpoints = ?, losepoints =? WHERE name = ? ;";
						PreparedStatement pstmt = (PreparedStatement) con.prepareStatement(statement);
						pstmt.setString(3, user);
						pstmt.setInt(1, winPoints);
						
						if (winpoints == 0){
							int losePoints = rs.getInt("losepoints");
							losePoints += 1;							
							pstmt.setInt(2, losePoints);	
						}
						else{
							int losePoints = rs.getInt("losepoints");
							pstmt.setInt(2, losePoints);
						}
						
						pstmt.executeUpdate();
					} catch (SQLException e) {
						System.out.println("SQLException: " + e.getMessage());
						System.out.println("SQLState: "+ e.getSQLState());
						System.out.println("VendorError: " + e.getErrorCode());
					}
				} 
				
				else {
					try {
						int losepoints=0;
						if (winpoints== 0){
							losepoints=1;
						}
						String insertNewUser = "INSERT INTO testtable (name, winpoints,losepoints)";
						insertNewUser += " VALUES ('" + user +"', '" + winpoints+ "', '" + losepoints+"')";
						System.out.println("Inserting new user: " + insertNewUser);
						stmt.executeUpdate(insertNewUser);
					} catch (SQLException e) {
						System.out.println("SQLException: " + e.getMessage());
						System.out.println("SQLState: "+ e.getSQLState());
						System.out.println("VendorError: " + e.getErrorCode());
					}
				}
			 
			 con.close();	 
			 }catch( Exception e ) {
			 e.printStackTrace();
			 }
	 }

						}//end class Jdbc10